package org.kiranputtur.phonebook;
import java.sql.*;
import java.util.List;

/**
 * Queries class responsible for setting database connection,and passing
 * all the SQL Queries to sql driver
 * @author Kiran Puttur
 * @category Final Project
 */

public class Queries {
	
	private static final String URL = "jdbc:mysql://localhost:3306/samples";
	private static final String USERNAME = "anonymous";
	private static final String PASSWORD = null;
	
	private Connection connection = null;
	private PreparedStatement  insertNewContact = null;
	private PreparedStatement  updateContact = null;
	private PreparedStatement  findContact = null;
	private PreparedStatement  deleteContact = null;


// constructor

public Queries()
	{
		try 
		{
			connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
		
			//Form a Query to insert contacts in the database
			insertNewContact = 
				connection.prepareStatement("INSERT INTO PHONE_BOOK  " +
						"(First,Last,Phone) " +
						"VALUES( ?, ?, ?);" );
			
			updateContact =
				connection.prepareStatement("UPDATE PHONE_BOOK  " +
						"SET First=?, Last=?, Phone=?" +
						"WHERE ID=? ");
			findContact =
				connection.prepareStatement("SELECT * FROM PHONE_BOOK  " +
						"WHERE First=? AND Last=?" );
			deleteContact =
				connection.prepareStatement("DELETE FROM PHONE_BOOK " + 
						"WHERE ID=?");
		}
		catch( SQLException sqlException)
		{
			sqlException.printStackTrace();
			System.exit( 1 );
		} //end catch
	}//end Queries block

//Add contact will the add the contact info to database
public int addContact( String fname, String lname, String phonenum)
{
	int result = 0;
	
	//Use try/catch block to set and execute INSERT operation
	try 
	{
		insertNewContact.setString(1, fname);
		insertNewContact.setString(2, lname);
		insertNewContact.setString(3,phonenum);
		
		//insert the new entry & return the result as "1" if successfull
		result = insertNewContact.executeUpdate();
	}
	catch ( SQLException sqlException)
	{
		sqlException.printStackTrace();
		close();
	}//end catch block
		return result;
	}//end addContact

public int updateContacts(String fname, String lname, String phonenum, int id)
{
	int result = 0;
	
	//use try/block to set parameters and execute Update SQL command
	try
	{
		updateContact.setString(1,fname);
		updateContact.setString(2,lname);
		updateContact.setString(3,phonenum);
		updateContact.setInt(4, id);
		
		//execute sql query
		result = updateContact.executeUpdate();
		
	} //end try
	catch(SQLException sqlException)
	{
		sqlException.printStackTrace();
		close();
	}//end catch block
	
		return result;
} // end updateContact method

// As per Professor  recent remark to Phil if multiple entries matches
// then you should return only the first one ignore the rest.
// so I am going to get all the found values and return the phone number
//top from the List

public Contact findContacts(String fname, String lname)
{
	Contact foundContact = null;
	ResultSet resultSet = null;
	
	try 
	{
		findContact.setString(1, fname);
		findContact.setString(2, lname);
		
		//Executequery return the results matching all fname and lname
		resultSet = findContact.executeQuery();
		
		if ( resultSet.next() )
		{
			foundContact = new Contact(
			resultSet.getInt("ID"),
			resultSet.getString("Last"),
			resultSet.getString("First"),
			resultSet.getString("Phone"));
			
		}//end of if
	}
		catch ( SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try 
			{
				resultSet.close();
			}
			catch (SQLException sqlException)
			{
				sqlException.printStackTrace();
				close();
			}
		}
		
		return foundContact;
} //end findContact Method.
		
public int deleteContacts(int id)
{
	int result = 0;
	
try 
	{
	   deleteContact.setInt(1, id);
	   
	   //Execute the sql query based on this
	   result = deleteContact.executeUpdate();
	   
	}
	catch ( SQLException sqlException)
	{
		sqlException.printStackTrace();
		close();
	}
	return result;
}
		
public void close()
{
	try
	{
		connection.close();
	}
	catch ( SQLException sqlException)
	{
		sqlException.printStackTrace();
	}//end catch
} // end method close
		

}